import json

import pandas as pd

from work_zykj.mysql import mysql_util
from work_zykj.oracle.oracle_util import oracle_util_class

"""查询手机号创建的场（一个连接中查询）"""


def find_in_oracle(cursor, df2, mobile, userId):
    sql = "select t.ORG_CODE, t.FARM_ORG, t.name_loc FARM_NAME, to_char(t.create_date,'yyyy/MM/dd') as 创建日期, t.user_create " \
          " ,'专业版' 类型 from fr_farm_org t where nvl(t.cancel_flag, 'N') = 'N' and t.management_flg = 'M' and t.org_code not like 'X%' " \
          "and t.create_date > to_date('2024/01/01', 'yyyy/MM/dd') and t.user_create like '%{}%' " \
          "order by t.org_code, t.farm_org ".format(mobile)

    rs = oracle_util.select2(sql, cursor)
    rs = json.loads(rs)
    # print(rs)
    # df2 = df2.append(rs)
    df = pd.DataFrame()
    for d in rs:
        d['手机号'] = mobile
        d['用户id'] = userId
        list_ = [d]
        df = pd.concat([df, pd.DataFrame(list_)], ignore_index=True)
    return df


def find_in_mysql(cursor_mysql, df2, mobile, userId):
    # 查询家庭版
    sql = "SELECT distinct a.farm_id ORG_CODE, a.farm_id as FARM_ORG,a.FARM_NAME,a.USER_CREATE,DATE_FORMAT(a.create_date, '%Y-%m-%d') 创建日期 " \
          " ,'家庭版' 类型 FROM bae_farm_information a inner join pc_user u on u.external_id = a.user_create " \
          " and a.create_date >= '2024-01-01 00:00:00' and u.mobile = '{}' " \
          " order by a.id".format(mobile)
    rs = mysql_util.select_by_sql2(sql, cursor_mysql)
    rs = json.loads(rs)
    df = pd.DataFrame()
    for d in rs:
        d['手机号'] = mobile
        d['用户id'] = userId
        list_ = [d]
        df = pd.concat([df, pd.DataFrame(list_)], ignore_index=True)
    return df


if __name__ == '__main__':
    file_path = 'C:/Users/yangjianzhang/Desktop/test3.xlsx'
    # 使用read_excel函数读取Excel文件并将其存储为DataFrame对象
    df = pd.read_excel(file_path, sheet_name='Sheet1')

    # 打印读取到的数据
    # print(df)
    df2 = pd.DataFrame()
    oracle_util = oracle_util_class()
    # nmdc的连接
    conn = oracle_util.get_connection('NMDCFARM')
    cursor = conn.cursor()
    # crdcfarm的连接
    conn_crdcfarm = oracle_util.get_connection('CRDCFARM')
    cursor_crdcfarm = conn_crdcfarm.cursor()
    # mysql的连接
    conn_mysql = mysql_util.get_connection()
    cursor_mysql = conn_mysql.cursor();
    for index, row in df.iterrows():
        mobile = row['手机号']
        userId = row['用户id']
        mobile = "%.0f" % mobile
        print(type(mobile))
        print(mobile)
        df2 = pd.concat([df2, find_in_oracle(cursor, df2, mobile, userId)], ignore_index=True)
        df2 = pd.concat([df2, find_in_oracle(cursor_crdcfarm, df2, mobile, userId)], ignore_index=True)
        df2 = pd.concat([df2, find_in_mysql(cursor_mysql, df2, mobile, userId)], ignore_index=True)

    cursor.close()
    conn.close()
    cursor_crdcfarm.close()
    conn_crdcfarm.close()
    cursor_mysql.close()
    conn_mysql.close()

    df2.to_excel('C:/Users/yangjianzhang/Desktop/test2.xlsx', index=False)
